{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1ba6b095-2a25-48f5-8dea-c2ef7a2e3046",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "数据验证:\n",
      "总记录数: 1000\n",
      "payment_date空值: 42 个\n",
      "shipping_date空值: 42 个\n",
      "用户ID重复情况: 199 个唯一用户\n",
      "商品种类: 30 种商品\n",
      "\n",
      "数据样例:\n",
      "  internal_order_number full_channel_user_id product_name  quantity  \\\n",
      "0     IN202511210000000         USER00000101           夹克         1   \n",
      "1     IN202508020000001         USER00000144           夹克         3   \n",
      "2     IN202511030000002         USER00000044          百褶裙         2   \n",
      "\n",
      "   product_amount status        payment_date  \n",
      "0          799.06    已完成 2025-11-21 09:42:07  \n",
      "1         2409.69    已取消                 NaT  \n",
      "2          274.16    已完成 2025-11-03 11:27:49  \n",
      "数据已成功保存到: D:\\数据分析及可视化\\erp_order_data.xlsx\n",
      "生成数据条数: 1000\n",
      "\n",
      "✅ 数据生成完成！\n",
      "📊 生成的数据统计:\n",
      "   订单状态分布:\n",
      "status\n",
      "已完成    653\n",
      "已发货    150\n",
      "已付款    102\n",
      "待付款     53\n",
      "已取消     42\n",
      "Name: count, dtype: int64\n",
      "   退款状态分布:\n",
      "refund_status\n",
      "未申请退款    852\n",
      "退款关闭      53\n",
      "退款中       48\n",
      "成功退款      47\n",
      "Name: count, dtype: int64\n",
      "   商品类别分布:\n",
      "product_name\n",
      "纯棉基础T恤    45\n",
      "商务衬衫      41\n",
      "半身裙       41\n",
      "修身款T恤     40\n",
      "包臀裙       39\n",
      "风衣        38\n",
      "牛仔衬衫      38\n",
      "羽绒服       38\n",
      "印花潮流T恤    37\n",
      "格子衬衫      36\n",
      "Name: count, dtype: int64\n",
      "   平台分布:\n",
      "platform\n",
      "天猫     186\n",
      "拼多多    181\n",
      "淘宝     172\n",
      "京东     161\n",
      "抖音     157\n",
      "微信     143\n",
      "Name: count, dtype: int64\n",
      "\n",
      "🔍 空值检查:\n",
      "   shipping_date: 42 个空值\n",
      "   payment_date: 42 个空值\n"
     ]
    }
   ],
   "source": [
    "# erp_order_data_generator_fixed.py\n",
    "import pandas as pd\n",
    "from faker import Faker\n",
    "import random\n",
    "from datetime import datetime, timedelta\n",
    "import os\n",
    "\n",
    "class ERPOrderDataGenerator:\n",
    "    def __init__(self):\n",
    "        self.fake = Faker('zh_CN')\n",
    "        # 初始化服装类商品数据\n",
    "        self.clothing_categories = {\n",
    "            'T恤': ['纯棉基础T恤', '印花潮流T恤', '修身款T恤', '宽松版T恤', '情侣款T恤'],\n",
    "            '衬衫': ['商务衬衫', '休闲衬衫', '条纹衬衫', '格子衬衫', '牛仔衬衫'],\n",
    "            '裤子': ['牛仔裤', '休闲裤', '运动裤', '工装裤', '西裤'],\n",
    "            '外套': ['夹克', '风衣', '羽绒服', '毛呢大衣', '牛仔外套'],\n",
    "            '毛衣': ['圆领毛衣', '高领毛衣', '针织开衫', '羊毛衫', '马海毛毛衣'],\n",
    "            '裙子': ['连衣裙', '半身裙', 'A字裙', '包臀裙', '百褶裙']\n",
    "        }\n",
    "        \n",
    "        self.colors = ['黑色', '白色', '灰色', '蓝色', '红色', '绿色', '黄色', '紫色', '粉色', '棕色']\n",
    "        self.sizes = ['S', 'M', 'L', 'XL', 'XXL']\n",
    "        self.specs = ['均码', '标准版', '修身版', '宽松版']\n",
    "        \n",
    "        self.stores = ['天猫官方旗舰店', '京东自营店', '抖音旗舰店', '拼多多品牌店', '微信小程序商城']\n",
    "        self.platforms = ['淘宝', '天猫', '京东', '抖音', '拼多多', '微信']\n",
    "        \n",
    "        self.provinces_cities = {\n",
    "            '北京市': ['北京市'],\n",
    "            '上海市': ['上海市'],\n",
    "            '广东省': ['广州市', '深圳市', '东莞市', '佛山市'],\n",
    "            '江苏省': ['南京市', '苏州市', '无锡市', '常州市'],\n",
    "            '浙江省': ['杭州市', '宁波市', '温州市', '绍兴市'],\n",
    "            '四川省': ['成都市', '绵阳市', '德阳市'],\n",
    "            '湖北省': ['武汉市', '黄石市', '襄阳市'],\n",
    "            '陕西省': ['西安市', '宝鸡市', '咸阳市'],\n",
    "            '辽宁省': ['沈阳市', '大连市', '鞍山市'],\n",
    "            '山东省': ['济南市', '青岛市', '烟台市']\n",
    "        }\n",
    "        \n",
    "        # 预生成用户ID池（允许重复）\n",
    "        self.user_pool = [f\"USER{str(i).zfill(8)}\" for i in range(1, 201)]\n",
    "        \n",
    "        # 预生成SPU和SKU池\n",
    "        self.spu_pool = []\n",
    "        self.sku_pool = []\n",
    "        self._generate_product_pool()\n",
    "    \n",
    "    def _generate_product_pool(self):\n",
    "        \"\"\"生成商品SPU和SKU池\"\"\"\n",
    "        spu_id = 1000\n",
    "        for category, products in self.clothing_categories.items():\n",
    "            for product in products:\n",
    "                spu = f\"SPU{spu_id}\"\n",
    "                self.spu_pool.append(spu)\n",
    "                \n",
    "                # 为每个SPU生成多个SKU（颜色+尺寸组合）\n",
    "                for color in self.colors[:3]:  # 每个SPU有3种颜色\n",
    "                    for size in self.sizes[:3]:  # 每个颜色有3个尺寸\n",
    "                        sku = f\"SKU{spu_id}{color[:2]}{size}\"\n",
    "                        self.sku_pool.append({\n",
    "                            'sku': sku,\n",
    "                            'spu': spu,\n",
    "                            'product_name': f\"{product}\",\n",
    "                            'color_and_spec': f\"{color}/{size}\",\n",
    "                            'category': category\n",
    "                        })\n",
    "                spu_id += 1\n",
    "    \n",
    "    def generate_order_data(self, count=1000):\n",
    "        \"\"\"生成ERP订单数据\"\"\"\n",
    "        orders = []\n",
    "        \n",
    "        # 修复：使用正确的日期格式\n",
    "        end_date = datetime.now()\n",
    "        start_date = end_date - timedelta(days=180)\n",
    "        \n",
    "        for i in range(count):\n",
    "            # 基础订单信息 - 修复日期生成\n",
    "            order_time = self.fake.date_time_between(start_date=start_date, end_date=end_date)\n",
    "            \n",
    "            # 确保付款日期和发货日期不为空\n",
    "            payment_date = order_time + timedelta(hours=random.randint(1, 24))\n",
    "            shipping_date = payment_date + timedelta(days=random.randint(1, 5))\n",
    "            \n",
    "            # 随机选择用户（允许重复）\n",
    "            user_id = random.choice(self.user_pool)\n",
    "            \n",
    "            # 随机选择商品\n",
    "            sku_info = random.choice(self.sku_pool)\n",
    "            \n",
    "            # 商品数量\n",
    "            quantity = random.randint(1, 3)\n",
    "            \n",
    "            # 价格计算\n",
    "            base_price_range = {\n",
    "                'T恤': (59, 199),\n",
    "                '衬衫': (99, 399),\n",
    "                '裤子': (129, 499),\n",
    "                '外套': (199, 899),\n",
    "                '毛衣': (149, 599),\n",
    "                '裙子': (129, 699)\n",
    "            }\n",
    "            \n",
    "            category = sku_info['category']\n",
    "            min_price, max_price = base_price_range[category]\n",
    "            unit_price = round(random.uniform(min_price, max_price), 2)\n",
    "            product_amount = round(unit_price * quantity, 2)\n",
    "            original_price = round(unit_price * random.uniform(1.1, 1.3), 2)\n",
    "            payable_amount = round(product_amount * random.uniform(0.95, 1.05), 2)\n",
    "            paid_amount = product_amount  # 假设实际支付金额等于商品金额\n",
    "            \n",
    "            # 收货地址\n",
    "            province = random.choice(list(self.provinces_cities.keys()))\n",
    "            city = random.choice(self.provinces_cities[province])\n",
    "            \n",
    "            # 订单状态逻辑\n",
    "            status_options = ['待付款', '已付款', '已发货', '已完成', '已取消']\n",
    "            weights = [0.05, 0.1, 0.15, 0.65, 0.05]  # 大部分订单为已完成状态\n",
    "            status = random.choices(status_options, weights=weights)[0]\n",
    "            \n",
    "            # 根据状态调整日期 - 修复：确保日期不为空\n",
    "            if status == '待付款':\n",
    "                # 对于待付款订单，设置合理的未来日期\n",
    "                payment_date = order_time + timedelta(hours=random.randint(24, 72))\n",
    "                shipping_date = payment_date + timedelta(days=random.randint(1, 5))\n",
    "                paid_amount = 0\n",
    "            elif status == '已付款':\n",
    "                # 对于已付款订单，发货日期设置为未来\n",
    "                shipping_date = payment_date + timedelta(days=random.randint(1, 5))\n",
    "            elif status == '已取消':\n",
    "                # 对于已取消订单，设置合理的日期逻辑\n",
    "                payment_date = None\n",
    "                shipping_date = None\n",
    "                paid_amount = 0\n",
    "            \n",
    "            # 退款状态逻辑\n",
    "            refund_status_options = ['未申请退款', '退款中', '成功退款', '退款关闭']\n",
    "            refund_weights = [0.85, 0.05, 0.05, 0.05]  # 大部分订单没有退款\n",
    "            refund_status = random.choices(refund_status_options, weights=refund_weights)[0]\n",
    "            \n",
    "            # 处理退款数量\n",
    "            registered_quantity = 0\n",
    "            actual_refund_quantity = 0\n",
    "            if refund_status in ['退款中', '成功退款']:\n",
    "                registered_quantity = random.randint(1, quantity)\n",
    "                if refund_status == '成功退款':\n",
    "                    actual_refund_quantity = registered_quantity\n",
    "            \n",
    "            order_data = {\n",
    "                'id': i + 1,\n",
    "                'internal_order_number': f\"IN{order_time.strftime('%Y%m%d')}{i:07d}\",\n",
    "                'online_order_number': f\"ON{order_time.strftime('%Y%m%d')}{random.randint(1000, 9999)}\",\n",
    "                'store_name': random.choice(self.stores),\n",
    "                'full_channel_user_id': user_id,\n",
    "                'shipping_date': shipping_date,\n",
    "                'payment_date': payment_date,\n",
    "                'payable_amount': payable_amount,\n",
    "                'paid_amount': paid_amount,\n",
    "                'status': status,\n",
    "                'consignee': self.fake.name(),\n",
    "                'spu': sku_info['spu'],\n",
    "                'order_time': order_time,\n",
    "                'province': province,\n",
    "                'city': city,\n",
    "                'platform': random.choice(self.platforms),\n",
    "                'sub_order_number': f\"SUB{order_time.strftime('%Y%m%d')}{i:06d}\",\n",
    "                'online_sub_order_number': f\"OSUB{order_time.strftime('%Y%m%d')}{random.randint(1000, 9999)}\",\n",
    "                'original_online_order_number': f\"OON{order_time.strftime('%Y%m%d')}{random.randint(10000, 99999)}\",\n",
    "                'sku': sku_info['sku'],\n",
    "                'quantity': quantity,\n",
    "                'unit_price': unit_price,\n",
    "                'product_name': sku_info['product_name'],\n",
    "                'color_and_spec': sku_info['color_and_spec'],\n",
    "                'product_amount': product_amount,\n",
    "                'original_price': original_price,\n",
    "                'is_gift': '否',\n",
    "                'sub_order_status': status,\n",
    "                'refund_status': refund_status,\n",
    "                'registered_quantity': registered_quantity,\n",
    "                'actual_refund_quantity': actual_refund_quantity\n",
    "            }\n",
    "            \n",
    "            orders.append(order_data)\n",
    "        \n",
    "        return pd.DataFrame(orders)\n",
    "    \n",
    "    def save_to_excel(self, df, file_path):\n",
    "        \"\"\"保存数据到Excel文件\"\"\"\n",
    "        try:\n",
    "            # 确保目录存在\n",
    "            os.makedirs(os.path.dirname(file_path), exist_ok=True)\n",
    "            \n",
    "            # 保存到Excel\n",
    "            with pd.ExcelWriter(file_path, engine='openpyxl') as writer:\n",
    "                df.to_excel(writer, sheet_name='erp_order', index=False)\n",
    "            \n",
    "            print(f\"数据已成功保存到: {file_path}\")\n",
    "            print(f\"生成数据条数: {len(df)}\")\n",
    "            \n",
    "        except Exception as e:\n",
    "            print(f\"保存文件时出错: {str(e)}\")\n",
    "            raise\n",
    "\n",
    "def main():\n",
    "    \"\"\"主函数\"\"\"\n",
    "    try:\n",
    "        # 初始化生成器\n",
    "        generator = ERPOrderDataGenerator()\n",
    "        \n",
    "        # 生成数据\n",
    "        df = generator.generate_order_data(1000)\n",
    "        \n",
    "        # 数据验证\n",
    "        print(\"\\n数据验证:\")\n",
    "        print(f\"总记录数: {len(df)}\")\n",
    "        print(f\"payment_date空值: {df['payment_date'].isnull().sum()} 个\")\n",
    "        print(f\"shipping_date空值: {df['shipping_date'].isnull().sum()} 个\")\n",
    "        print(f\"用户ID重复情况: {df['full_channel_user_id'].nunique()} 个唯一用户\")\n",
    "        print(f\"商品种类: {df['product_name'].nunique()} 种商品\")\n",
    "        \n",
    "        # 显示数据样例\n",
    "        print(\"\\n数据样例:\")\n",
    "        sample_data = df.head(3)[[\n",
    "            'internal_order_number', 'full_channel_user_id', 'product_name', \n",
    "            'quantity', 'product_amount', 'status', 'payment_date'\n",
    "        ]]\n",
    "        print(sample_data)\n",
    "        \n",
    "        # 保存文件\n",
    "        output_path = r\"D:\\数据分析及可视化\\erp_order_data.xlsx\"\n",
    "        generator.save_to_excel(df, output_path)\n",
    "        \n",
    "        print(\"\\n✅ 数据生成完成！\")\n",
    "        print(\"📊 生成的数据统计:\")\n",
    "        print(f\"   订单状态分布:\\n{df['status'].value_counts()}\")\n",
    "        print(f\"   退款状态分布:\\n{df['refund_status'].value_counts()}\")\n",
    "        print(f\"   商品类别分布:\\n{df['product_name'].value_counts().head(10)}\")\n",
    "        print(f\"   平台分布:\\n{df['platform'].value_counts()}\")\n",
    "        \n",
    "        # 检查是否有空值\n",
    "        print(f\"\\n🔍 空值检查:\")\n",
    "        for column in df.columns:\n",
    "            null_count = df[column].isnull().sum()\n",
    "            if null_count > 0:\n",
    "                print(f\"   {column}: {null_count} 个空值\")\n",
    "        \n",
    "    except Exception as e:\n",
    "        print(f\"❌ 数据生成失败: {str(e)}\")\n",
    "        import traceback\n",
    "        traceback.print_exc()\n",
    "\n",
    "if __name__ == \"__main__\":\n",
    "    main()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a80d115-7ef3-4ca9-8eee-0e61eaae6ce4",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
